USE [HR_DB]
GO
if exists(select * from dbo.sysobjects where id = object_id(N'trigger_r_user_role_del')   and   OBJECTPROPERTY(id,   N'IsTRIGGER')   =   1)   
  DROP TRIGGER trigger_r_user_role_del
GO 
-- =============================================
-- Author:杨洁(kaixuanpisces)
-- Create date: 20090827
-- Description:	用户角色关系表的删除触发器
-- =============================================
CREATE TRIGGER [dbo].[trigger_r_user_role_del] 
   ON  [dbo].[r_user_role]
   INSTEAD OF DELETE
AS 
BEGIN
	DECLARE @errors AS int
	SET @errors=0
	DECLARE @CurRow int,@u_id int,@role_id int,@start_time smalldatetime
	BEGIN TRANSACTION
	DECLARE [Cursor] CURSOR FOR SELECT id FROM DELETED
	OPEN [Cursor]
	FETCH NEXT FROM [Cursor] INTO @CurRow
	WHILE(@@FETCH_STATUS<>-1)
	BEGIN
		IF(@@FETCH_STATUS<>-2)
		BEGIN
			SELECT @u_id=u_id,@role_id=role_id,@start_time=start_time FROM DELETED WHERE id=@CurRow
			INSERT INTO r_user_role_bak VALUES(@CurRow,@u_id,@role_id,@start_time,GetDate(),0)
			SET @errors=@errors+@@ERROR
			DELETE FROM r_user_role WHERE id=@CurRow
			SET @errors=@errors+@@ERROR
		END
		FETCH NEXT FROM [Cursor] INTO @CurRow
	END
	CLOSE [Cursor]
	DEALLOCATE [Cursor]
	IF @errors<>0
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION
END
GO
